﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ePlanet.SingleWell.Models;
using CommonModule;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;

namespace ePlanet.SingleWell.DataAccess
{
    public class WellCatalogDAL : DataHelper
    {
        public WellCatalogDAL()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public void AddNewCatalog(WellCatalog catalog)
        {
            this.InsertData(catalog);
        }

        public void UpdateWellCatalog(WellCatalog catalog)
        {
            if (catalog != null && catalog.WELLCATALOG.Count > 0)
            {
                foreach (WellCatalog.WELLCATALOGRow oRow in catalog.WELLCATALOG)
                {
                    oRow.SetModified();
                }
                catalog.AcceptChanges();
            }

            this.InsertData(catalog);
        }

        public void DeleteWellCatalog(string cataogID)
        {
            string sql = string.Format("delete from wellcatalog where wellcatalogID='{0}'",cataogID);
            this.DBHelper.ExecuteSql(sql);
        }

        public DataTable GetAllWellCatalogOld()
        {
            DataTable rtnDataTable = null;
            string sql = string.Format("select * from wellcatalog");
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        public WellCatalog.WELLCATALOGDataTable GetAllWellCatalog()
        {
            string sql = "select * from wellcatalog";
            WellCatalog.WELLCATALOGDataTable data = new WellCatalog.WELLCATALOGDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ds.Tables[0].TableName = data.TableName;
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public WellCatalog GetWellCatalogByID(string catalogID)
        {
            WellCatalog rtnCatalog = null;
            string sql = string.Format("select * from wellcatalog where wellcatalogID='{0}'", catalogID);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnCatalog = new WellCatalog();
                rtnCatalog.WELLCATALOG.Merge(oDataSet.Tables[0]);
                return rtnCatalog;
            }
            else
            {
                return rtnCatalog;
            }
        }

        public WellCatalog GetWellCatalogByParentID(string parentID)
        {
            WellCatalog rtnCatalog = null;
            string sql = string.Format("select * from wellcatalog where PARENTID='{0}'", parentID);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnCatalog = new WellCatalog();
                rtnCatalog.WELLCATALOG.Merge(oDataSet.Tables[0]);
                return rtnCatalog;
            }
            else
            {
                return rtnCatalog;
            }
        }


        public WellCatalog GetWellCataLogByParam(string param)
        {
            WellCatalog rtnCatalog = null;
            string sql = string.Format("SELECT distinct * FROM Wellcatalog START WITH catalogDescript like '%{0}%' CONNECT BY PRIOR parentID = wellcatalogid ", param);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnCatalog = new WellCatalog();
                rtnCatalog.WELLCATALOG.Merge(oDataSet.Tables[0]);
                return rtnCatalog;
            }
            else
            {
                return rtnCatalog;
            }
        }

        public WellCatalog GetBaseCatalog()
        {
            WellCatalog rtnCatalog = null;
            string sql = string.Format("select * from wellcatalog where PARENTID='{0}'", 0);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnCatalog = new WellCatalog();
                rtnCatalog.WELLCATALOG.Merge(oDataSet.Tables[0]);
                return rtnCatalog;
            }
            else
            {
                return rtnCatalog;
            }
        }

        public DataTable GetWellCatalogByPage(int page, int pageSize, string parentId,bool isPage,string searchLevel, string searchParam)
        {
            DataTable rtnDataTable = null;
            if (this.DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                string dataSql = string.Empty;
                if (isPage)
                {
                    dataSql = string.Format("select B.*, (select count(*) from wellcatalog where parentid = B.wellcatalogid) ccount, (select count(*) from singlewell where wellcatalogid = B.wellcatalogid) wellcount from (select A.*, rownum NumberIndex from (select * from wellcatalog where parentid='{0}' ) A where rownum < {2}) B where NumberIndex >= {1}", parentId, (page - 1) * pageSize + 1, (page - 1) * pageSize + 1 + pageSize);
                }
                else
                {
                    if (searchLevel != "1")
                    {
                        if (string.IsNullOrEmpty(searchParam))
                        {
                            throw new Exception("深度搜索条件不能为空");
                        }
                        dataSql = string.Format(@"select A.*, (select count(*) from wellcatalog where parentid = A.wellcatalogid) ccount, 
                                    (select count(*) from singlewell where wellcatalogid = A.wellcatalogid) wellcount from 
                                    (select distinct * FROM Wellcatalog START WITH catalogDescript {0} CONNECT BY PRIOR parentID = wellcatalogid) A", searchParam);
                        DataSet oDataSet = this.DBHelper.ExecuteDataSet(dataSql);
                        if (oDataSet != null && oDataSet.Tables.Count > 0)
                        {
                            List<DataRow> removeRows = new List<DataRow>();
                            //DataRow[] rowArray = oDataSet.Tables[0].Select(" WELLCATALOGID = '"+parentId+"'");
                            //while (rowArray != null && rowArray.Length > 0)
                            //{ 
                            //    removeRows.Add(rowArray[0]);
                            //    rowArray = oDataSet.Tables[0].Select(" WELLCATALOGID = '" + removeRows.Last()["PARENTID"].ToString() + "'");
                            //}
                            //foreach (DataRow row in removeRows)
                            //{
                            //    oDataSet.Tables[0].Rows.Remove(row);
                            //}
                            foreach (DataRow row in oDataSet.Tables[0].Rows)
                            {
                                if (row != null)
                                {
                                    if (row["WELLCATALOGID"].ToString() != parentId)
                                    {
                                        string pId = row["PARENTID"].ToString();
                                        while (pId != null && pId != parentId)
                                        {
                                            DataRow[] array = oDataSet.Tables[0].Select(" WELLCATALOGID = '" + pId + "'");
                                            if (array != null && array.Length > 0)
                                            {
                                                if (array.Length == 1)
                                                {
                                                    pId = array[0]["PARENTID"].ToString(); ;
                                                }
                                                else
                                                {
                                                    throw new Exception("WELLCATALOGID违反主键约束");
                                                }
                                            }
                                            else
                                            {
                                                pId = null;
                                            }
                                        }
                                        if (pId == null)
                                        {
                                            removeRows.Add(row);
                                        }
                                    }
                                    else
                                    {
                                        removeRows.Add(row);
                                    }
                                }
                            }
                            foreach (DataRow row in removeRows)
                            {
                                oDataSet.Tables[0].Rows.Remove(row);
                            }
                            rtnDataTable = oDataSet.Tables[0];
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(searchParam))
                        {
                            dataSql = string.Format("select B.*, (select count(*) from wellcatalog where parentid = B.wellcatalogid) ccount, (select count(*) from singlewell where wellcatalogid = B.wellcatalogid) wellcount from wellcatalog B where parentid='{0}' and catalogDescript {1}", parentId, searchParam);
                        }
                        else
                        {
                            dataSql = string.Format("select B.*, (select count(*) from wellcatalog where parentid = B.wellcatalogid) ccount, (select count(*) from singlewell where wellcatalogid = B.wellcatalogid) wellcount from wellcatalog B where parentid='{0}'", parentId);
                        }

                        DataSet oDataSet = this.DBHelper.ExecuteDataSet(dataSql);
                        if (oDataSet != null && oDataSet.Tables.Count > 0)
                        {
                            rtnDataTable = oDataSet.Tables[0];
                        }
                    }
                }
                //int count = 0;
                //string countSql = string.Format("select count(*) from wellcatalog where parentid = '{0}'", parentId);
                //object obj = this.DBHelper.ExecuteScalar(countSql);
                //if (obj != null)
                //{
                //    count = int.Parse(obj.ToString());
                //}
                //rtnDataTable.Columns.Add("CCOUNT");
                //foreach (DataRow row in rtnDataTable.Rows)
                //{
                //    row["CCOUNT"] = count;
                //}
            }
            else
            {
                throw new Exception("此系统只支持oracle");
            }
            return rtnDataTable;
        }

        public void DeleteWellCatalogAll(string cataogID)
        {
            try
            {
                this.DBHelper.RunProcedure("DeleteWellCatalog", new string[] { "wellId" }, new object[] { cataogID }, new DbType[] { DbType.String });
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
